"""
Copyright 2012 Wieser Dietmar, Margit Mutschlechner.

This file is part of Multimedia Social Networking Platform.

Multimedia Social Networking Platform is free software: 
you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

Multimedia Social Networking Platform is distributed
in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with Multimedia Social Networking Platform.  
If not, see <http://www.gnu.org/licenses/>.

"""

import traceback
from msnp.logger import Logger
from msnp.model.MediaModel import SynchChanges
from msnp.model import MediaModel
from msnp.persistence.mysql import DBErrorHandler

########
#tables#
########
TABLE_SYNCH_CHANGES = "synch_changes"

#################################
#columns for synch_changes table#
#################################

COLUMN_SC_ID = "id"
COLUMN_SC_TABLENAME = "tablename"
COLUMN_SC_SORTORDER = "sortorder"
COLUMN_SC_ROWID = "row_id"
COLUMN_SC_COLUMNNAME = "columnname"
COLUMN_SC_PLAYER = "player"
COLUMN_SC_EVENT = "event"
COLUMN_SC_OLD = "old"
COLUMN_SC_NEW = "new"
COLUMN_SC_CHANGE_TIME = "change_time"


def executeQuery(database, cursor, query):
    """Executes the given query on the given database.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        query: A String containing the query to be executed.
    """
    try:
        cursor.execute(query)
        database.commit()
        return True
    except:
        Logger.info("error while executing query: " + query)
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.UNKNOWN, traceback.format_exc())
    return False
        
def selectSynchInsertChanges(database, cursor, schema):
    """Selects all insert entries of the synch_changes table, i.e. entries with the value INSERT at the column "event".
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A list of SynchChanges objects or None if an error occurred.
    """
    query = __getQuerySelectInsertChanges(schema)
    return __selectSynchChanges(database, cursor, schema, query)     

def selectSynchUpdateChanges(database, cursor, schema):
    """Selects all update entries of the synch_changes table, i.e. entries with the value UPDATE at the column "event".
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        
    Returns:
        A list of SynchChanges objects or None if an error occurred.
    """
    query = __getQuerySelectUpdateChanges(schema)
    return __selectSynchChanges(database, cursor, schema, query)    

def selectSynchChangesSize(database, cursor, schema):
    """Retrieves the number of entries of the SynchChanges table.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        
    Returns:
        An integer number representing the number of entries of the SynchChanges table or 0 if an error occurred.
    """
    size = 0
    try:
        query = __getQuerySelectSynchChangesSize(schema)
        cursor.execute(query)
        result = cursor.fetchone()
        if result != None and len(result) > 0 and result[0]!=None:
            size = int(result[0])
    except:
        Logger.info("Error: unable to select synch changes size")
        Logger.info(traceback.format_exc())
        size = 0
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return size 
        
def __selectSynchChanges(database, cursor, schema, query):
    """Selects entries of the synch_changes table according to the given query.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        query: A String containing the query that should be executed.
    
    Returns:
        A list of SynchChanges objects or None if an error occurred.
    """
    try:
        Logger.mediaDBinfo(query)
        cursor.execute(query)
        changes = []
        while 1:
            result = cursor.fetchone()
            if result == None:
                break
            id = int(result[0])
            tablename = result[1]
            sortorder = int(result[2])
            rowId = int(result[3])
            columnname = result[4]
            player = result[5]
            event = result[6]
            old = result[7]
            new = result[8]
            changeTime = result[9]
            c = SynchChanges(id, tablename, sortorder, rowId, columnname, player, event, old, new, changeTime)
            changes.append(c)
        return changes
    except:
        Logger.info("Error: unable to select synch_changes")
        Logger.info(traceback.format_exc())
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    return None

def deleteSynchChanges(database, cursor, schema):
    """Deletes all entries of the NetSynchChanges table.
    
    Args:
        database: A Connection object representing the database where the query should be executed.
        cursor: A Cursor object needed for the execution of the database query.
        schema: A String containing the name of the schema where the query should be executed.
        
    Returns:
        True, if the deletion was successful, False otherwise.
    """
    try:
        Logger.info("delete all synch changes")
        #delete users
        query = __getQueryDeleteSynchChanges(schema)
        cursor.execute(query)
        database.commit()
        return True
    except:
        Logger.info("Error: unable to delete old synch changes")
        Logger.info(traceback.format_exc())
        database.rollback()
        DBErrorHandler.processMediaDBError(DBErrorHandler.DBErrorType.DELETE, traceback.format_exc())
    return False

####################
## SELECT_QUERIES ##
####################

def __getQuerySelectInsertChanges(schema):
    """Creates a query for selecting all insert changes.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query.
    """
    query = "select * from " + schema + "." + TABLE_SYNCH_CHANGES
    query += " where " + COLUMN_SC_EVENT + " = '" + MediaModel.SynchChangeEvent.INSERT
    query += "' order by " + COLUMN_SC_SORTORDER + ", " + COLUMN_SC_ID
    return query

def __getQuerySelectUpdateChanges(schema):
    """Creates a query for selecting all update changes.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
    
    Returns:
        A String containing the created query.
    """
    query = "select * from " + schema + "." + TABLE_SYNCH_CHANGES
    query += " where " + COLUMN_SC_EVENT + " = '" + MediaModel.SynchChangeEvent.UPDATE
    query += "' order by " + COLUMN_SC_ID
    return query

def __getQuerySelectSynchChangesSize(schema):
    """Creates a query for selecting the number of entries in the SynchChanges table.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
        length: An integer value indicating the number of groups to delete.
    
    Returns:
        A String containing the created query.
    """
    query = "select count(*) from " + schema + "." + TABLE_SYNCH_CHANGES
    return query

def __getQueryDeleteSynchChanges(schema):
    """Creates a query for deleting all entries of the SynchChanges table.
    
    Args:
        schema: A String containing the name of the schema where the query should be executed.
        length: An integer value indicating the number of groups to delete.
    
    Returns:
        A String containing the created query.
    """
    query = "delete from " + schema + "." + TABLE_SYNCH_CHANGES + " where " + COLUMN_SC_CHANGE_TIME
    return query
    
